Excel BI - Excel Challenge 681

excel-challenges

excel-formulas

🔰 Split and align the data as shown.

Published

March 24, 2026

Illustration for Excel BI - Excel Challenge 681

Challenge Description

🔰 Split and align the data as shown.

Solutions

library(tidyverse)
library(readxl)

path = "Excel/681 Split and Align.xlsx"
input = read_excel(path, range = "A1:A5")
test  = read_excel(path, range = "C2:G12", col_names = c("1", "2", "3", "4", "5"))

result = input %>%
  mutate(group= row_number()) %>%
  separate_rows(Data, sep = ", ") %>%
  mutate(col = row_number(), .by = group) %>%
  mutate(row = row_number()) %>%
  pivot_wider(names_from = col, values_from = Data) %>%
  select(-c(group, row))

all.equal(result, test, check.attributes = FALSE) # TRUE

Logic: Read the workbook ranges needed for the challenge; Derive the required intermediate columns; Parse the packed text or string structure; Aggregate or rank the data at the required grouping level.
Strengths: The reshaping step mirrors the workbook output closely instead of forcing extra post-processing.
Areas for Improvement: The solution assumes the workbook layout and selected ranges remain stable, so any structural change in the sheet would require small adjustments.
Gem: The last reshape turns a raw transformation into something that already looks like a report.

import pandas as pd

path = "681 Split and Align.xlsx"
input = pd.read_excel(path, usecols="A", nrows=4)
test = pd.read_excel(path, usecols="C:G", nrows=11, names=["1", "2", "3", "4", "5"])

input["RowNumber"] = input.index + 1
input = input.assign(**{"Split": input["Data"].str.split(", ")}).explode("Split")
input["row"] = range(1, len(input) + 1)
input["col"] = input.groupby("RowNumber").cumcount() + 1
input = input.pivot(index="row", columns="col", values="Split").reset_index(drop=True)
input.columns.name = None
input.columns = test.columns


print(test.equals(input)) # True

The Python version follows the same grouped logic and keeps the transformation explicit in a dataframe pipeline.

Difficulty Level

Medium

The individual steps are manageable, but the correct transformation pattern is not obvious from the raw data.